#!/usr/bin/perl -w

# This script is used to dump the all city data of china 
# Author : RainX
use DBI;

my $sourceDir;
$sourceDir  = "D:/databases/train_ticket/orig";

# get the file list

opendir(TDIR, $sourceDir);
my @lists;
my $f;
while($f = readdir(TDIR))
{
    next if $f eq ".";
    next if $f eq "..";
    push(@lists, $f);
}
closedir(TDIR);

my $dbh = DBI->connect("dbi:SQLite:dbname=/databases/train_ticket/tickets","","");

# get pro_list;

my $cityName;

# for each it
foreach my $pFile (@lists)
{
    my $tmpTxt = getAll($sourceDir."/".$pFile);
    $tmpTxt =~s/.* <span class=redb>([^<]*).*/\1/g; 
    # print $sourceDir."/".$pFile."\n";
    #print $province."\n";
    chomp($tmpTxt);
    $proId  = getProId($tmpTxt);
    print $proId."\n";
    $tmpTxt2 = getAll($sourceDir."/".$pFile);
    # print $tmpTxt2;
    while ($tmpTxt2 =~ /chaxun\/resultz\.asp\?txtChezhan=([^ >]*)>/g)
    {
        $cityName = $1;
        $dbh->do("replace into city (province_id, city_name) values ($proId, '$cityName')");
    }
}
$dbh->disconnect();

# get all content of file
sub getAll
{
    my ($fileName) = @_;
    open (CUTEFILE, "<$fileName");
    my @all = <CUTEFILE>;
    close(CUTEFILE);
    return join("", @all);
}

# get proid
sub getProId
{
    my ($proName)   = @_;
    my $statment    = $dbh->prepare("select id from province where province_name='$proName'");
    $statment->execute();
    my ($proId)     = $statment->fetchrow_array();
    return $proId;
}
